﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Odbc;
using System.Configuration;

public class CategoriaDAL
{

    public int idnextval { get; set; }
    public int idCategoria { get; set; }
    public string Titulo { get; set; }
    public string Link { get; set; }
    public int Ordem { get; set; }

    public int totProduto { get; set; }
    public string Ativo { get; set; }
    public int idUpdate { get; set; }
    public string DataUpdate { get; set; }
    public int idCadastro { get; set; }
    public string DataCadastro { get; set; }
    public string msgRetorno { get; set; }
    public int codRetorno { get; set; }

    public int idProduto { get; set; }

    public string comandoDelete = "DELETE FROM tbCategoria WHERE idCategoria = ? ";
    public string comandoSelect = "SELECT * FROM tbCategoria WHERE idCategoria = ? ";
    public string comandoInsert;
    public string comandoUpdate;
    public int totProd;

    public CategoriaDAL()
    {
        this.idCategoria = next_val();
        this.Titulo = "";
        this.Link = "";
        this.Ordem = 0;
        this.Ativo = "S";
        this.idUpdate = 0;
        this.DataUpdate = DateTime.Now.ToString("dd / MM / yyyy h:mm:ss");
        this.idCadastro = 0;
        this.DataCadastro = DateTime.Now.ToString("dd / MM / yyyy h:mm:ss");
    }
    public static List<CategoriaDAL> SelecionarTodos(string param)
    {
        try
        {
            string comandSelectTodos = "SELECT * FROM tbCategoria ";

            if (param != "")
            {
                //comandSelectTodos = "SELECT * FROM tbCategoria WHERE Login LIKE '%" + param.ToString() + "%' ";
                comandSelectTodos = "SELECT * FROM tbCategoria WHERE Titulo = ?  ";
            }

            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand(comandSelectTodos, con))
            {
                List<CategoriaDAL> lCategoria = new List<CategoriaDAL>();

                cmd.Parameters.Add("@Titulo", OdbcType.VarChar, 255).Value = param;
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {

                    CategoriaDAL oObj = new CategoriaDAL();
                    oObj.idCategoria = Convert.ToInt32(rs["idCategoria"]);
                    oObj.Titulo = rs["Titulo"].ToString();
                    /*
					oObj.Link = rs["Link"].ToString();
                    oObj.Ordem = Convert.ToInt32(rs["Ordem"]);
                    oObj.Ativo = rs["Ativo"].ToString();
                    oObj.idUpdate = Convert.ToInt32(rs["idUpdate"]);
                    oObj.DataUpdate = rs["DataUpdate"].ToString();
                    oObj.idCadastro = Convert.ToInt32(rs["idCadastro"]);
                    oObj.DataCadastro = rs["DataCadastro"].ToString();
					*/
                    oObj.msgRetorno = "Registo encontrado";
                    oObj.codRetorno = 0;
                    lCategoria.Add(oObj);
                }
                rs.Close();
                con.Close();
                return lCategoria;
            }
        }
        catch (Exception ex)
        {

            List<CategoriaDAL> lCategoria = new List<CategoriaDAL>();
            CategoriaDAL oObj = new CategoriaDAL();
            oObj.msgRetorno = "Registro nao encontrado";
            oObj.codRetorno = 1;
            lCategoria.Add(oObj);
            return lCategoria;

        }
    } // SelecionarTodos

    public static List<CategoriaDAL> SelecionarMenu()
    {
        try
        {
            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand("SELECT * FROM vwProdutoCategoria ", con))
            {
                List<CategoriaDAL> lCategoria = new List<CategoriaDAL>();
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {

                    CategoriaDAL oObj = new CategoriaDAL();
                    oObj.idCategoria = Convert.ToInt32(rs["idCategoria"]);
                    oObj.idProduto = Convert.ToInt32(rs["idProduto"]);
                    oObj.Titulo = rs["Titulo"].ToString();
                    /*
					oObj.Link = rs["Link"].ToString();
                    oObj.Ordem = Convert.ToInt32(rs["Ordem"]);
                    oObj.Ativo = rs["Ativo"].ToString();
                    oObj.idUpdate = Convert.ToInt32(rs["idUpdate"]);
                    oObj.DataUpdate = rs["DataUpdate"].ToString();
                    oObj.idCadastro = Convert.ToInt32(rs["idCadastro"]);
                    oObj.DataCadastro = rs["DataCadastro"].ToString();
					*/
                    oObj.msgRetorno = "Registo encontrado";
                    oObj.codRetorno = 0;
                    lCategoria.Add(oObj);
                }
                rs.Close();
                con.Close();
                return lCategoria;
            }
        }
        catch (Exception ex)
        {

            List<CategoriaDAL> lCategoria = new List<CategoriaDAL>();
            CategoriaDAL oObj = new CategoriaDAL();
            oObj.msgRetorno = "Registro nao encontrado";
            oObj.codRetorno = 1;
            lCategoria.Add(oObj);
            return lCategoria;

        }
    } // SelecionarMenu

    public int next_val()
    {
        try
        {
            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand("SELECT TOP 1 idCategoria FROM tbCategoria ORDER BY idCategoria DESC ", con))
            {
                idnextval = 1;
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    idnextval = Convert.ToInt32(rs["idCategoria"]) + 1;
                }
            }
            return idnextval;

        }
        catch (Exception ex)
        {
            return 0;
        }

    }
    public void SelecionarPorID(int id)
    {
        try
        {
            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand(comandoSelect, con))
            {
                cmd.Parameters.Add("@idCategoria", OdbcType.Int).Value = id;
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    this.idCategoria = Convert.ToInt32(rs["idCategoria"]);
                    this.Titulo = rs["Titulo"].ToString();
                    this.Link = rs["Link"].ToString();
                    this.Ordem = Convert.ToInt32(rs["Ordem"]);
                    this.Ativo = rs["Ativo"].ToString();
                    this.idUpdate = Convert.ToInt32(rs["idUpdate"]);
                    this.DataUpdate = rs["DataUpdate"].ToString();
                    this.idCadastro = Convert.ToInt32(rs["idCadastro"]);
                    this.DataCadastro = rs["DataCadastro"].ToString();

                    this.msgRetorno = "Registro encontrado";
                    this.codRetorno = 0;
                }
                rs.Close();
                con.Close();
            }

        }
        catch (Exception ex)
        {
            this.msgRetorno = "Registro não encontrado";
            this.codRetorno = 1;
        }
    } // SelecionarPorID
    public void Insert()
    {
        comandoInsert = "INSERT INTO tbCategoria (idCategoria,Titulo,Link,Ordem, Ativo, idUpdate,idCadastro) VALUES (?,?,?,?,?,?,?) ";


        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand(comandoInsert, con))
        {
            cmd.Parameters.Add("@idCategoria", OdbcType.Int).Value = this.idCategoria;
            cmd.Parameters.Add("@Titulo", OdbcType.VarChar, 255).Value = this.Titulo;
            cmd.Parameters.Add("@Link", OdbcType.VarChar, 255).Value = this.Link;
            cmd.Parameters.Add("@Ordem", OdbcType.Int).Value = Convert.ToInt32(this.Ordem);
            cmd.Parameters.Add("@Ativo", OdbcType.VarChar, 255).Value = this.Ativo;
            cmd.Parameters.Add("@idUpdate", OdbcType.Int).Value = Convert.ToInt32(this.idUpdate);
            cmd.Parameters.Add("@idCadastro", OdbcType.Int).Value = Convert.ToInt32(this.idCadastro);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            this.msgRetorno = "Registro inserido com sucesso";
            this.codRetorno = 0;
        }
    }
    public void Update(int id)
    {
        comandoUpdate = "UPDATE tbCategoria SET Titulo=? ,Link=? , Ordem=?,Ativo=?,idUpdate=? ,DataUpdate=? WHERE idCategoria = ? ";

        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand(comandoUpdate, con))
        {
            cmd.Parameters.Add("@Titulo", OdbcType.VarChar, 255).Value = this.Titulo;
            cmd.Parameters.Add("@Link", OdbcType.VarChar, 255).Value = this.Link;
            cmd.Parameters.Add("@Ordem", OdbcType.Int).Value = this.Ordem;
            cmd.Parameters.Add("@Ativo", OdbcType.VarChar, 255).Value = this.Ativo;
            cmd.Parameters.Add("@idUpdate", OdbcType.Int).Value = this.idUpdate;
            cmd.Parameters.Add("@DataUpdate", OdbcType.VarChar, 255).Value = this.DataUpdate;
            cmd.Parameters.Add("@idCategoria", OdbcType.Int).Value = id;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            this.msgRetorno = "Registro atualizado com sucesso";
            this.codRetorno = 0;
        }
    }

    public void Delete(int id)
    {
        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand(comandoDelete, con))
        {

            cmd.Parameters.Add("@idCategoria", OdbcType.Int).Value = id;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    } //Delete

    public int totalProduto(int id)
    {
        try
        {
            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) as totProduto FROM tbProdutoCategoria WHERE idCategoria = ? ", con))
            {
                cmd.Parameters.Add("@idCategoria", OdbcType.Int).Value = id;
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    totProd = Convert.ToInt32(rs["totProduto"]);
                }
                rs.Close();
                con.Close();
            }
            return totProd;

        }
        catch (Exception ex)
        {
            return 0;
        }
    } // SelecionarPorID
}